-- XF17 P11 : CREATES DATABASE STRUCTURE
-- Last modified : 12/05/2011  
-- -------------------------------------
-- 
-- Database: `xf17`
--
-- ------------------------------------

--
-- TODO : AJOUTER PLUS DE CONTRAINTES
--

DROP TABLE IF EXISTS TypeRole;
DROP TABLE IF EXISTS Prix; --
DROP TABLE IF EXISTS TypePersonne CASCADE; --
DROP TABLE IF EXISTS Creneau CASCADE; -- j'ai fusionn√© date et heure en d√©but timestamp + nomSalle ne reference pas entierement une salle. j'ai ajout√© deux foreign keys
DROP TABLE IF EXISTS Spectacle; --
DROP TABLE IF EXISTS Artiste; --
DROP TABLE IF EXISTS Salle; --
DROP TABLE IF EXISTS Commune; --
DROP TABLE IF EXISTS Spectateur CASCADE;
DROP TABLE IF EXISTS Utilisateur CASCADE;
DROP TABLE IF EXISTS Comporte;
DROP TABLE IF EXISTS Reservation;




-- SUPER FONCTION DE CALCUL DU NOMBRE DE PLACES RESTANTES
/*select distinct s.nbplace -
(select (sum(c.nombre)) from reservation r, comporte c, creneau q
WHERE r.numeroreserv=c.numeroreserv AND q.pkcreneau=r.creneau AND q.nomspectacle='NOMDUSPECTACLE')
FROM creneau q, salle s
WHERE q.nomspectacle='NOMDUSPECTACLE' AND q.nomsalle=s.nomsalle;*/

-- manque Spectateur et Reservation (implique Spectateur) et Comporte (implique reservation


CREATE TABLE Commune (
CPCommune CHAR(5) PRIMARY KEY,
nomCommune VARCHAR(50)
CHECK ((char_length(CPCommune) = 5) AND ((CPCommune < '99999') AND (CPCommune > '00000')))
);

CREATE TABLE Salle (
CPCommune CHAR(5),
nomSalle VARCHAR(50),
adresseSalle VARCHAR(100),
nbPlace INTEGER,
PRIMARY KEY (CPCommune, nomSalle),
FOREIGN KEY (CPCommune) REFERENCES Commune(CPCommune) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Artiste (
PKartiste INTEGER,
nomArtiste VARCHAR(50) NOT NULL,
prenomArtiste VARCHAR(50) NOT NULL,
PRIMARY KEY (PKartiste),
UNIQUE (nomArtiste, prenomArtiste)
);

CREATE TABLE Spectacle (
nomSpectacle VARCHAR(50) PRIMARY KEY,
libType VARCHAR(50),
directeur INTEGER REFERENCES Artiste(PKartiste) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Creneau (
PKCreneau INTEGER PRIMARY KEY,
debut TIMESTAMP,
nomSalle VARCHAR(50) NOT NULL,
CPCommune CHAR(5) NOT NULL,
nomSpectacle VARCHAR(50),
UNIQUE  (nomSalle, CPCommune,PKCreneau),
FOREIGN KEY (nomSpectacle) REFERENCES Spectacle(nomSpectacle) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (nomSalle,CPCommune) REFERENCES Salle(nomSalle,CPCommune) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE TypePersonne (
categorie VARCHAR(50) PRIMARY KEY --  a changer en enum
);

CREATE TABLE Prix (
nomSpectacle VARCHAR(50) REFERENCES Spectacle(nomSpectacle) ON DELETE CASCADE ON UPDATE CASCADE,
categorie VARCHAR(50) REFERENCES TypePersonne(categorie),
prixPlace FLOAT,
PRIMARY KEY (nomSpectacle, categorie),
UNIQUE (nomSpectacle, categorie)
);

CREATE TABLE TypeRole (
nomSpectacle VARCHAR(50) REFERENCES Spectacle(nomSpectacle) ON DELETE CASCADE ON UPDATE CASCADE,
PKartiste INTEGER REFERENCES Artiste(PKartiste) ON UPDATE CASCADE ON DELETE CASCADE,
role VARCHAR(50),
PRIMARY KEY (nomSpectacle, PKartiste)
);

CREATE TABLE Utilisateur (
email VARCHAR(50) PRIMARY KEY,
password VARCHAR(50),
isAdmin boolean
);

CREATE TABLE Spectateur (
email VARCHAR(50) REFERENCES Utilisateur(email) ON DELETE CASCADE ON UPDATE CASCADE,
nomSpec VARCHAR(50),
prenomSpec VARCHAR(50),
dateDeNaissance DATE,
adresseSpec VARCHAR(50),
CPSpec CHAR(5),
telephone CHAR(10),
PRIMARY KEY (email),
CHECK ((CPSpec < '99999') AND (CPSpec > '00000') AND (telephone > '0000000000' ) AND (telephone < '9999999999'))
);

CREATE TABLE Reservation (
numeroReserv INTEGER PRIMARY KEY,
dateReserv DATE,
heure TIME,
nomReserv VARCHAR(50) NOT NULL,
creneau INTEGER NOT NULL,

FOREIGN KEY (nomReserv) REFERENCES Spectateur(email) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (creneau) REFERENCES Creneau(PKCreneau) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Comporte (
numeroReserv INTEGER REFERENCES Reservation(numeroReserv) ON DELETE CASCADE ON UPDATE CASCADE,
categorie VARCHAR(50) REFERENCES TypePersonne(categorie) ON DELETE CASCADE ON UPDATE CASCADE,
nombre INTEGER,
CHECK (nombre>0),
PRIMARY KEY (numeroReserv, categorie)
);

CREATE OR REPLACE FUNCTION deleteCat() RETURNS TRIGGER AS
$deleteCat$
	BEGIN
		DELETE FROM typepersonne t
		WHERE NOT EXISTS (
		SELECT p.categorie
		FROM prix p
		WHERE t.categorie=p.categorie);
		RETURN NEW;
	END;
$deleteCat$ LANGUAGE plpgsql;

CREATE TRIGGER deleteCat AFTER DELETE ON prix
FOR EACH ROW EXECUTE PROCEDURE deleteCat();

CREATE OR REPLACE FUNCTION getNBPlace(numcreneau INTEGER, nomduspectacle VARCHAR) RETURNS integer AS $$ 
DECLARE
nombreplace INTEGER;
BEGIN
	SELECT distinct s.nbplace -
			(select (sum(c.nombre)) from reservation r, comporte c, creneau q INTO nombreplace
			WHERE r.numeroreserv=c.numeroreserv AND q.pkcreneau=numcreneau AND q.pkcreneau=r.creneau AND q.nomspectacle=nomduspectacle)
		AS nb_place
		FROM creneau q, salle s
		WHERE q.nomspectacle=nomduspectacle AND q.nomsalle=s.nomsalle;
	
	IF (nombreplace IS NULL) THEN
				select s.nbplace INTO nombreplace
				FROM creneau q, salle s
				WHERE q.nomspectacle=nomduspectacle AND q.pkcreneau=numcreneau AND q.nomsalle=s.nomsalle;
	END IF;
	
	RETURN nombreplace;
END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW administrateur (email, password) AS
	SELECT email, password
	FROM utilisateur
	WHERE isadmin = true;

CREATE OR REPLACE VIEW utilisateurnormaux (email, password) AS
	SELECT email, password
	FROM utilisateur
	WHERE isadmin = false;

